package com.ccp.dev.workflow.service;

import com.ccp.dev.core.basic.api.IPosition;
import com.ccp.dev.core.basic.api.ISysOrg;
import com.ccp.dev.core.basic.api.ISysUser;
import com.ccp.dev.core.basic.db.datasource.JdbcTemplateUtil;
import com.ccp.dev.core.basic.engine.FreemarkEngine;
import com.ccp.dev.core.basic.engine.GroovyScriptEngine;
import com.ccp.dev.core.basic.table.TableModel;
import com.ccp.dev.core.basic.util.ContextUtil;
import com.ccp.dev.core.util.*;
import com.ccp.dev.form.consts.FormConstants;
import com.ccp.dev.form.dao.FormFieldDao;
import com.ccp.dev.form.model.*;
import com.ccp.dev.form.service.*;
import com.ccp.dev.form.util.FieldPool;
import com.ccp.dev.form.util.FormUtil;
import com.ccp.dev.form.util.PlatformType;
import com.ccp.dev.system.model.Position;
import com.ccp.dev.system.model.SysOrg;
import com.ccp.dev.system.model.SysUser;
import com.ccp.dev.workflow.bpmutil.ServiceUtil;
import com.ccp.dev.workflow.dao.ProcessRunDao;
import com.ccp.dev.workflow.model.Identity;
import com.ccp.dev.workflow.model.ProcessRun;
import com.ccp.dev.workflow.model.TaskOpinion;
import freemarker.template.TemplateException;
import net.sf.json.JSONObject;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.io.IOException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 自定义表单数据处理类
 *
 * @author zks
 * @version 2019-07-01
 */
@Service
public class FormHandlerService {

    protected Logger logger = LoggerFactory.getLogger(FormHandlerService.class);
    private static final Pattern pattern = Pattern.compile("<script([\\s\\S]*?)script>");
    // 流程实例分隔符
    private String INSTANCEID_SPLITOR = "#instanceId#";
    // 流程示意图替换符
    private String FLOW_CHART_SPLITOR = "(?s)<div[^>]*?\\s+name=\"editable-input\"\\s+class=\"flowchart\">(.*?)</div>";
    private String FLOW_CHART_SPLITOR_IE = "(?s)<div[^>]*?\\s+class=\"flowchart\"\\s+name=\"editable-input\">(.*?)</div>";

    @Resource
    private FormFieldDao formFieldDao;
    @Resource
    private FormControlService formControlService;
    @Resource
    private FormRightsService formRightsService;
    @Resource
    private FreemarkEngine freemarkEngine;
    @Resource
    private FormDefService formDefService;
    @Resource
    private FormTableService formTableService;
    @Resource
    private TaskOpinionService taskOpinionService;
    @Resource
    private IdentityService identityService;
    @Resource
    private DataTemplateService dataTemplateService;
    @Resource
    private ProcessRunDao processRunDao;
    @Resource
    private SubTableRightsService subTableRightsService;
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private ProcessRunService processRunService;
    @Resource
    private SysBusEventService sysBusEventService;
    @Resource
    private GroovyScriptEngine groovyScriptEngine;

    /**
     * 获取表单数据
     *
     * @param procDefId
     * @param tableId
     * @return FormData
     */
    private FormData getFormData(String procDefId, String tableId) {
        FormData data = new FormData();
        List<FormField> list = formFieldDao.getByTableIdContainHidden(tableId);
        Map<String, Object> resultMap = this.calculateFormData(list, data, procDefId);
        data.setMainFields(resultMap);
        return data;
    }

    /**
     * 根据不同情况（脚本或者表单输入）计算表单数据
     *
     * @param list
     * @param data
     * @param procDefId
     * @return
     */
    private Map<String, Object> calculateFormData(List<FormField> list, FormData data, String procDefId) {
        Map<String, Object> resultMap = new HashMap<String, Object>();
        ISysUser user = ContextUtil.getCurrentUser();
        ISysOrg org = ContextUtil.getCurrentOrg();
        IPosition pos = ContextUtil.getCurrentPos();
        for (FormField field : list) {
            String fieldName = field.getFieldName().toLowerCase();
            // 值来源为脚本。
            if (field.getValueFrom() == FormField.VALUE_FROM_SCRIPT_SHOW) {
                Object result = FormUtil.calcuteField(field.getScript(), data.getMainFields());
                resultMap.put(fieldName, result);
            }
            // 计算默认日期数据
            else if (field.getControlType() == 15 || "date".equals(field.getFieldType())) {
                String prop = field.getCtlProperty();
                // {"format":"yyyy-MM-dd","displayDate":1,"condition":"like"}
                if (StringUtil.isNotEmpty(prop)) {
                    try {
                        JSONObject jsonObject = JSONObject.fromObject(prop);
                        if (jsonObject.containsKey("displayDate")) {
                            String format = jsonObject.getString("format");
                            String displayDate = jsonObject.getString("displayDate");
                            if ("1".equals(displayDate)) {
                                resultMap.put(fieldName, TimeUtil.getDateString(format));
                            }
                        }
                    } catch (Exception ex) {
                        logger.debug(ex.getMessage());
                    }
                }
            }
            // 用户选择器默认当前用户
            else if (field.getControlType().shortValue() == FieldPool.SELECTOR_USER_SINGLE) {
                String prop = field.getCtlProperty();
                if (StringUtil.isNotEmpty(prop)) {
                    JSONObject jsonObject = JSONObject.fromObject(prop);
                    if (jsonObject.containsKey("showCurUser")) {
                        String showCurUser = JSONObject.fromObject(prop).getString("showCurUser");
                        if ("1".equals(showCurUser)) {
                            if (field.getIsHidden() == 1) {
                                resultMap.put(fieldName, user.getUserId());
                            } else {
                                resultMap.put(fieldName, user.getFullName());
                            }
                        }
                    }

                }
            } else if (FieldPool.SELECTOR_ORG_SINGLE == field.getControlType().shortValue() && BeanUtils.isNotEmpty(org)) {
                String prop = field.getCtlProperty();
                if (StringUtil.isNotEmpty(prop)) {
                    JSONObject jsonObject = JSONObject.fromObject(prop);
                    if (jsonObject.containsKey("showCurOrg")) {
                        String showCurUser = JSONObject.fromObject(prop).getString("showCurOrg");
                        if ("1".equals(showCurUser)) {
                            if (field.getIsHidden() == 1) {
                                resultMap.put(fieldName, org.getOrgId());
                            } else {
                                resultMap.put(fieldName, org.getOrgName());
                            }
                        }
                    }

                }
            } else if (FieldPool.SELECTOR_POSITION_SINGLE == field.getControlType() && BeanUtils.isNotEmpty(pos)) {
                String prop = field.getCtlProperty();
                if (StringUtil.isNotEmpty(prop)) {
                    JSONObject jsonObject = JSONObject.fromObject(prop);
                    if (jsonObject.containsKey("showCurPos")) {
                        String showCurUser = JSONObject.fromObject(prop).getString("showCurPos");
                        if ("1".equals(showCurUser)) {
                            if (field.getIsHidden() == 1) {
                                resultMap.put(fieldName, pos.getPosId());
                            } else {
                                resultMap.put(fieldName, pos.getPosName());
                            }
                        }
                    }

                }
            }
            //值来源为表单输入
            else if (field.getValueFrom() == 0) {
                resultMap.put(fieldName, data.getMainFields().get(fieldName));
            }

        }
        return resultMap;
    }


    /**
     * 获取新增编辑表单页面的html
     *
     * @param def         表单
     * @param pkValue     主键
     * @param procDefId   流程id
     * @param nodeId      节点id
     * @param contextPath 上下文
     * @return
     * @throws Exception
     */
    public Map<String, Object> formObtainHtml(FormDef def, String pkValue, String procDefId, String nodeId, String contextPath) throws Exception {
        Map<String, Object> dataMap = new HashMap<>();

        String tableId = def.getTableId();
        String template = def.getTemplate();
        String formKey = def.getFormKey();
        FormData data = getFormData(tableId, pkValue, "", procDefId, nodeId, false);

        //编辑时候解密主子表数据
        if (StringUtil.isNotEmpty(pkValue)) {
            dataTemplateService.decryptMainSubFieldMap(data, tableId, FormField.ISENCRYPT_NOT);
        }
        Map<String, Map> model = new HashMap<>();
        model.put("main", data.getMainFields());
        model.put("opinion", data.getOptions());
        model.put("sub", data.getSubTableMap());

        Map<String, Object> map = new HashMap<>();
        map.put("model", model);
        map.put("service", formControlService);
        String userId = ContextUtil.getCurrentUserId();
        map.put("permission", formRightsService.getByFormKeyAndUserId(formKey, userId, procDefId, nodeId, PlatformType.PC));

        map.put("contextPath", contextPath);
        String html = freemarkEngine.parseByStringTemplate(map, template);

        // 流程图解析
        if (StringUtil.isNotEmpty(procDefId)) {
            String repStr = "<iframe src=\"" + "&actDefId=" + procDefId + "\"  name=\"flowchart\" id=\"flowchart\" marginwidth=\"0\" marginheight=\"0\" frameborder=\"0\" scrolling=\"no\" width=\"100%\"></iframe>";
            html = html.replaceAll(FLOW_CHART_SPLITOR, repStr).replaceAll(FLOW_CHART_SPLITOR_IE, repStr);
        }
        // 子表权限放到页面 （在页面解析的）
        // 子表字段相关权限
        Map<String, List<JSONObject>> subFileJsonMap;
        subFileJsonMap = formRightsService.getSubTablePermission(formKey, ContextUtil.getCurrentUserId(), procDefId, nodeId, PlatformType.PC);

        String subFilePermissionMap = "{}";
        if (BeanUtils.isNotEmpty(subFileJsonMap)) {
            subFilePermissionMap = JSONObject.fromObject(subFileJsonMap).toString();
        }
        html += "<script type=\"text/javascript\" > var subFilePermissionMap = " + subFilePermissionMap + " </script>";
        dataMap.put("html", html);
        dataMap.put("mainFields", data.getMainFields());
        dataMap.put("subFields", data.getSubTableList());
        dataMap.put("permission", map.get("permission"));
        return dataMap;
    }


    /**
     * 获取tab的html。
     *
     * @param tabTitle
     * @param html
     * @return
     * @throws TemplateException
     * @throws IOException
     */
    private String getTabHtml(String tabTitle, String html) throws TemplateException, IOException {
        String[] aryTitle = tabTitle.split(FormDef.PageSplitor);
        String[] aryHtml = html.split(FormDef.PageSplitor);

        List<Map<String, String>> list = new ArrayList<Map<String, String>>();
        for (int i = 0; i < aryTitle.length; i++) {
            Map<String, String> map = new HashMap<String, String>();
            map.put("title", aryTitle[i]);
            map.put("html", aryHtml[i]);
            list.add(map);
        }
        String formPath = FormTemplateService.getFormTemplatePath() + "tab.html";
        String tabTemplate = FileUtil.readFile(formPath);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("tabList", list);
        String output = freemarkEngine.parseByStringTemplate(map, tabTemplate);

        // 分时把js移到tab外面防止ligerTab二次加载js
        Matcher matcher = pattern.matcher(output);
        while (matcher.find()) {
            String str = matcher.group();
            output = output.replace(str, "");
            output = str + "\n" + output;
        }

        return output;
    }


    /**
     * 根据表单定义，用户ID，主键获取表单的html代码。
     *
     * <pre>
     * 实现流程：
     * 1.获取表单的模版。
     * 2.判断主键是否为空。
     * 		1.主键为空。
     * 			实例化BpmFormData数据。
     * 			1.判断字段值来源是流水号。
     * 				如果是流水号,则生成流水号。
     * 			2.判断字段值是来自脚本。
     * 				通过脚本计算得出字段的值。
     * 			3.如果是日期控件，或者日期。
     * 				默认显示时间的话，根据日期格式获取当前日期。
     * 		2.主键不为空。
     * 			根据主键获取表单的数据。
     *
     * 3.将数据和字段权限给模版引擎解析，生成html。
     * </pre>
     *
     * @param formDef
     * @param businessKey
     * @param processRun
     * @param ctxPath
     * @param iscopyFlow
     * @return
     * @throws Exception
     */
    public Map<String, Object> getFormDetail(FormDef formDef, String businessKey, ProcessRun processRun, String ctxPath, boolean iscopyFlow) throws Exception {
        Map<String, Object> dataMap = new HashMap<>();
        String tableId = formDef.getTableId();
        String template = formDef.getTemplate();
        String instanceId = "";
        String procDefId = "";
        if (BeanUtils.isNotEmpty(processRun)) {
            instanceId = processRun.getProcInstId();
            procDefId = processRun.getProcDefId();
        }
        FormData data = getFormData(tableId, businessKey, instanceId, procDefId, "", iscopyFlow);
        //点击明细解密主子表数据
        dataTemplateService.decryptMainSubFieldMap(data, tableId, FormField.ISENCRYPT_NOT);

        Map<String, Map> model = new HashMap<>(20);
        model.put("main", data.getMainFields());
        model.put("opinion", data.getOptions());
        model.put("sub", data.getSubTableMap());

        Map<String, Object> map = new HashMap<>(20);
        map.put("model", model);
        // 传入控制器的service，用于在模版中解析字段。
        map.put("service", formControlService);
        map.put("permission", formRightsService.getByFormKey(formDef));
        String html = freemarkEngine.parseByStringTemplate(map, template);

        String tabTitle = formDef.getTabTitle();
        if (null != tabTitle && tabTitle.indexOf(FormDef.PageSplitor) > -1) {
            html = getTabHtml(tabTitle, html);
        }

        // 子表权限放到页面 （在页面解析的）并虚构子表权限 避免页面解析出错
        FormTable formTable = formTableService.getTableByIdContainHidden(tableId);
        html += getSubPermission(formTable, true);
        dataMap.put("html", html);
        dataMap.put("mainFields", data.getMainFields());
        dataMap.put("subFields", data.getSubTableList());
        return dataMap;
    }

    /**
     * 虚构子表的字符串权限并变成 JS变量 的字符串
     *
     * @param formTable
     * @param onlyRead
     */
    public String getSubPermission(FormTable formTable, boolean onlyRead) {
        String html = "";
        // 子表字段权限
        List<FormTable> tableList = formTable.getSubTableList();
        Map<String, List<JSONObject>> subFileJsonMap = new HashMap<String, List<JSONObject>>();
        List<JSONObject> subJsonList = new ArrayList<JSONObject>();
        List<JSONObject> subTableShowList = new ArrayList<JSONObject>();
        for (FormTable table : tableList) {
            // 子表事个表的权限
            JSONObject permission = null;
            if (onlyRead) {
                permission = formRightsService.getReadPermissionJson(table.getTableName(), table.getTableDesc(), 4);
            } else {
                permission = formRightsService.getPermissionJson(table.getTableName(), table.getTableDesc(), 4);
            }
            permission.put("tableId", table.getTableId());
            permission.put("tableName", table.getTableName());
            permission.put("mainTableId", formTable.getTableId());
            permission.put("mainTableName", formTable.getTableName());
            subTableShowList.add(permission);

            // 每个子表中的每个字段
            List<FormField> subFieldList = table.getFieldList();
            for (FormField field : subFieldList) {
                // 子表事个表的权限
                JSONObject subPermission = null;
                if (onlyRead) {
                    subPermission = formRightsService.getReadPermissionJson(field.getFieldName(), field.getFieldDesc(), 1);
                    subPermission.put("power", "r");
                } else {
                    subPermission = formRightsService.getPermissionJson(field.getFieldName(), field.getFieldDesc(), 1);
                    subPermission.put("power", "w");
                }

                // 增加字段的控件类型
                subPermission.put("controlType", field.getControlType());

                subPermission.put("tableId", table.getTableId());
                subPermission.put("tableName", table.getTableName());
                subPermission.put("mainTableId", formTable.getTableId());
                subPermission.put("mainTableName", formTable.getTableId());
                subJsonList.add(subPermission);
            }
        }
        subFileJsonMap.put("subFileJsonList", subJsonList);
        subFileJsonMap.put("subTableShowList", subTableShowList);

        String subFilePermissionMap = "{}";
        if (BeanUtils.isNotEmpty(subFileJsonMap)) {
            subFilePermissionMap = JSONObject.fromObject(subFileJsonMap).toString();
        }
        html += "<script type=\"text/javascript\" > var subFilePermissionMap = " + subFilePermissionMap + " </script>";
        return html;
    }

    /**
     * 根据表ID，主键值，流程实例Id 获取bpmFormData 实例
     *
     * @param tableId
     * @param pkValue
     * @param instanceId
     * @return
     * @throws Exception
     */
    public FormData getFormData(String tableId, String pkValue, String instanceId, String procDefId, String nodeId, boolean isCopyFlow) throws Exception {
        FormData data;
        if (StringUtil.isNotEmpty(pkValue)) {
            // 处理业务数据模板的情况。如果actDefId=#dataTem 说明是从业务数据模板调用该方法的，需要在这里处理为空 即 actDefId="";
            if ("#dataTem".equals(procDefId) || "#formPrev".equals(procDefId)) {
                procDefId = "";
            }
            // 根据主键和表取出数据填充BpmFormData。
            data = getByKey(tableId, pkValue, procDefId, nodeId, true);
            // 获取表单的意见。
            if (StringUtil.isNotEmpty(instanceId)) {

                Map<String, String> formOptions = getFormOptionsByInstance(instanceId);
                if (BeanUtils.isNotEmpty(formOptions)) {
                    data.setOptions(formOptions);
                }
            }
            // 如果是复制流程重新计算流水号和脚本计算结果
            if (isCopyFlow) {
                // 获取流水号和脚本计算结果
                List<FormField> list = formFieldDao.getByTableIdContainHidden(tableId);
                Map<String, Object> resultMap = this.getCalculateDataMap(list, data, procDefId);
                data.setMainFields(resultMap);
                // 将子表中的主键值去掉
                if (data.getSubTableList() != null && data.getSubTableList().size() > 0) {
                    for (SubTable subTable : data.getSubTableList()) {
                        //子表主键
                        String pkName = subTable.getPkName().toLowerCase();
                        List<Map<String, Object>> dataList = subTable.getDataList();
                        for (Map<String, Object> dataMap : dataList) {
                            dataMap.remove(pkName);
                        }
                    }
                }
            }
        } else {
            data = new FormData();
            // 获取流水号和脚本计算结果
            List<FormField> list = formFieldDao.getByTableIdContainHidden(tableId);
            Map<String, Object> resultMap = this.getCalculateDataMap(list, data, procDefId);
            // 将流水号和脚本计算结果加入data
            data.setMainFields(resultMap);
        }

        return data;
    }

    /**
     * 根据表ID，当前流程定义ID,流程节点ID查询业务数据。
     *
     * @param tableId  表ID
     * @param pkValue  主键
     * @param actDefId 流程定义ID
     * @param nodeId   节点ID
     * @return
     * @throws Exception
     */
    public FormData getByKey(String tableId, String pkValue, String actDefId, String nodeId, boolean isHandleData) throws Exception {
        //主表的表单模板
        FormTable mainFormTableDef = formTableService.getByTableId(tableId, FormField.FIELD_NORMAL_HIDDEN);
        // 获取jdbctemplate对象。
        JdbcTemplate jt = JdbcTemplateUtil.getNewJdbcTemplate(mainFormTableDef.getDsAlias());
        //主表名
        String tableName = mainFormTableDef.getTableName();
        //主键字段
        String pkField = mainFormTableDef.getPkField();
        //主键值
        PkValue pk = new PkValue(pkField, pkValue);
        // 取得主表的数据
        Map<String, Object> mainData = getByKey(jt, pk, mainFormTableDef, isHandleData);
        //创建formData，并将tableId、tableName、dsAlias、isExternal、formTable从formTable中获取到
        FormData formData = new FormData(mainFormTableDef);
        // 取子表的数据
        List<FormTable> tableList = mainFormTableDef.getSubTableList();
        for (FormTable table : tableList) {
            SubTable subTable = new SubTable();
            String fk = table.getRelation();
            String subPk = table.getPkField();
            List<Map<String, Object>> list = getByFk(table, pk.getValue().toString(), actDefId, nodeId, isHandleData);
            subTable.setTableName(table.getTableName().toLowerCase());
            subTable.setDataList(list);
            subTable.setFkName(fk);
            subTable.setPkName(subPk);
            formData.addSubTable(subTable);
        }
        formData.setTableId(tableId);
        formData.setTableName(tableName);
        formData.setPkValue(pk);
        formData.addMainFields(mainData);
        return formData;
    }

    //TODO 识别主子表，重新定义ProcessRun里的tableId、pKField、pkValue、mainData、subTable
    public FormData getUrlForm(String tableId, ProcessRun processRun, String actDefId, String nodeId) throws Exception {
        //主表的表单模板

        //主表名
        String tableName = processRun.getTableName();
        //主键 ，其中参数为主键字段、主键值
        PkValue pk = new PkValue(processRun.getPkName(), processRun.getBusinessKey());
        // 主表的数据
//        Map<String, Object> mainData = getByKey(jt, pk, mainFormTableDef, isHandleData);
        // TODO 取子表的数据

        //赋值
        FormData formData = new FormData();
//        formData.setTableId(tableId);
        formData.setTableName(tableName);
        formData.setPkValue(pk);
//        formData.addMainFields(mainData);
        return null;
    }

    /**
     * 根据主键查询一条数据。
     *
     * @param jdbcTemplate
     * @param pk
     * @param formTable
     * @param isHandleData
     * @return
     */
    public Map<String, Object> getByKey(JdbcTemplate jdbcTemplate, PkValue pk, FormTable formTable, boolean isHandleData) {
        String sql;
        if (formTable.getIsExternal() == FormTable.NOT_EXTERNAL) {
            sql = "select a.* from " + "W_" + formTable.getTableName() + " a where " + pk.getName() + "= '" + pk.getValue() + "'";
        } else {
            sql = "select a.* from " + formTable.getTableName() + " a where " + pk.getName() + "= '" + pk.getValue() + "'";
        }
        List<FormField> fieldList = formTable.getFieldList();
        Map<String, FormField> fieldMap = convertToMap(fieldList);
        Map<String, Object> map;
        try {
            map = jdbcTemplate.queryForMap(sql);
            if (isHandleData) {
                map = handMap(formTable, fieldMap, map);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            logger.error(ex.getMessage());
            map = new HashMap<>();
        }
        return map;
    }

    /**
     * 根据外键查询列表数据。
     *
     * @param table
     * @param fkValue
     * @param procDefId
     * @param nodeId
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> getByFk(FormTable table, String fkValue, String procDefId, String nodeId, boolean isHandleData) throws Exception {

        List<FormField> fieldList = table.getFieldList();

        JdbcTemplate jt = ServiceUtil.getJdbcTemplate(table.getDsAlias());

        String sql = getSql(table, procDefId, nodeId, fkValue);

        Map<String, FormField> fieldMap = convertToMap(fieldList);

        List<Map<String, Object>> list = jt.queryForList(sql);
        if (!isHandleData) {
            return list;
        }
        List<Map<String, Object>> rtnList = new ArrayList<Map<String, Object>>();

        for (Map<String, Object> map : list) {
            Map<String, Object> obj = handMap(table, fieldMap, map);
            rtnList.add(obj);
        }
        return rtnList;
    }

    /**
     * 获取子表的SQL语句。
     *
     * @param table
     * @param procDefId
     * @param nodeId
     * @param fkValue
     * @return
     */
    private String getSql(FormTable table, String procDefId, String nodeId, String fkValue) {
        String subTableName = table.getFactTableName();
        String fkField = table.getRelation();
        String pkField = table.getPkField();
        short keyDataType = table.getKeyDataType();


        String sql = "";

        String tableId = table.getTableId();
        Short isMain = table.getIsMain();
        boolean isExternal = table.isExtTable();

        if (!isExternal) {

            //数字类型
            if (keyDataType == 0) {
                sql = "select a.* from " + subTableName + " a,bpm_bus_link b where  a." + pkField + "=b.bus_pk and a." + fkField + "='" + fkValue + "'";
            } else {
                sql = "select a.* from " + subTableName + " a,bpm_bus_link b where  a." + pkField + "=b.bus_pkstr and  a." + fkField + "='" + fkValue + "'";
            }


            // todo 处理子表权限
            /*String limitSql = getLimitSql(tableId, procDefId, nodeId, fkValue);
            // 处理子表权限
            if (!StringUtil.isEmpty(limitSql)) {
                sql += " " + limitSql;
            }*/
            if (isMain == 0) {
                sql += " order by a.sort";
            }else{
                sql += " order by b.BUS_ID";
            }
        } else {
            if (keyDataType == 0) {
                sql = "select * from " + subTableName + " where " + fkField + "='" + fkValue + "'";
            } else {
                sql = "select * from " + subTableName + " where " + fkField + "='" + fkValue + "'";
            }
            sql += " order by " + table.getPkField();
        }


        return sql;
    }

    /**
     * 处理返回的map数据 将key转换成小写。 如果是本地数据库，则替换字段前缀。 如果数据是日期类型，则转换数据格式。
     *
     * @param fieldMap
     * @param map
     */
    private Map<String, Object> handMap(FormTable table, Map<String, FormField> fieldMap, Map<String, Object> map) {
        Set<Class> excludeFormator = excludeFormatObjects.get();
        if (excludeFormator == null) {
            excludeFormator = new HashSet<Class>();
        }

        String pkField = table.getPkField();

        Map<String, Object> rtnMap = new HashMap<String, Object>();
        for (Map.Entry<String, Object> entry : map.entrySet()) {
            String fieldName = entry.getKey().toLowerCase();
//
            String key = fieldName;
            Object obj = entry.getValue();
            if (obj == null) {
                rtnMap.put(key, "");
                continue;
            }
            if (pkField.equalsIgnoreCase(key)) {
                rtnMap.put(key, obj);
                continue;
            }
            FormField formField = fieldMap.get(key);
            if (formField == null) {
                continue;
            }

            // 对时间字段单独处理。
            if (obj instanceof Date) {

                String format = "yyyy-MM-dd";
                if (formField != null) {
                    String confFormat = formField.getPropertyMap().get("format");
                    if (StringUtil.isNotEmpty(confFormat)) {
                        format = confFormat;
                    }
                }
                String str = TimeUtil.getDateTimeString((Date) obj, format);
                rtnMap.put(key, str);
            } else if (obj instanceof Number && !excludeFormator.contains(Number.class)) {
                Map<String, String> paraMap = formField.getPropertyMap();
                Object isShowComdify = paraMap.get("isShowComdify");
                Object decimalValue = paraMap.get("decimalValue");
                Object coinValue = paraMap.get("coinValue");

                String str = StringUtil.getNumber(obj, isShowComdify, decimalValue, coinValue);
                rtnMap.put(key, str);
            } else {

                //附件替换
                /*if (BeanUtils.isNotEmpty(formField) && BeanUtils.isNotEmpty(formField.getControlType()) && formField.getControlType().shortValue() == FieldPool.ATTACHEMENT) {
                    obj = ((String) obj).replace("\"", "￥@@￥");
                }*/
                obj = obj.toString().replaceAll("\n", "<br/>");
                rtnMap.put(key, obj);
            }
        }
        return rtnMap;
    }

    // 排除不需要format的字段类型。
    private static ThreadLocal<Set<Class>> excludeFormatObjects = new ThreadLocal<Set<Class>>();


    /**
     * 根据流程定义id，节点id和表ID获取子表显示条件的SQL片段。
     *
     * @param tableId
     * @param actDefId
     * @param nodeId
     * @return
     */
    private String getLimitSql(String tableId, String actDefId, String nodeId, String pkValue) {
        if (StringUtil.isEmpty(actDefId) || StringUtil.isEmpty(nodeId)) {
            return "";
        }
        String limitSql = "";

        ProcessRun processRun = processRunDao.getByBusinessKeyAndProcDefId(pkValue, actDefId);
        String parentActDefId = getParentProcessRunActDefId(processRun);
        SubTableRights rule = null;
        if (StringUtil.isEmpty(parentActDefId)) {
            rule = subTableRightsService.getByProcDefIdAndNodeId(actDefId, nodeId, tableId);
        } else {
            rule = subTableRightsService.getByProcDefIdAndNodeIdAndParent(actDefId, nodeId, tableId, parentActDefId);
        }

        if (rule == null) {
            return "";
        }
        int permissionType = rule.getPermissionType().intValue();
        // 处理权限,生成sql约束片段.
        switch (permissionType) {
            case 0:// 简单配置:判断用户
                String userId = ContextUtil.getCurrentUserId();
                limitSql = " and b.BUS_CREATOR_ID   = " + userId;
                break;
            case 1:// 简单配置:判断组织
                SysOrg org = (SysOrg) ContextUtil.getCurrentOrg();
                if (org == null) {
                    limitSql = " and 1 = 2";// 强制查询不到
                } else {
                    limitSql = " and b.BUS_ORG_ID = " + org.getOrgId();
                }
                break;
            case 2:// 脚本
                GroovyScriptEngine scriptEngine = (GroovyScriptEngine) AppUtil.getBean(GroovyScriptEngine.class);
                limitSql = scriptEngine.executeString(rule.getPermissionSetting(), new HashMap<String, Object>());// 不需流程变量入参
                break;
            default:
                break;
        }
        return limitSql;

    }

    /**
     * 获取父流程定义ID
     *
     * @param processRun
     * @return
     */
    public String getParentProcessRunActDefId(ProcessRun processRun) {
        if (BeanUtils.isNotEmpty(processRun) && StringUtil.isNotEmpty(processRun.getParentId())) {
            ProcessRun parentProcessRun = processRunDao.getById(processRun.getParentId());
            if (BeanUtils.isNotEmpty(parentProcessRun)) {
                return parentProcessRun.getProcDefId();
            }
        }
        return "";
    }

    /**
     * 修正字段名
     *
     * @param fieldName 字段名
     * @param source    数据来源 1.表示自定义表（需要加F_修正）
     * @param prefix    前缀修正
     * @return
     */
    private String fixFieldName(String fieldName, String source, String prefix) {
        if (StringUtils.isEmpty(fieldName) || StringUtils.isEmpty(source)) {
            return fieldName;
        }
        if ("0".equals(source)) {
            fieldName = fieldName;
        }
        if (StringUtils.isNotEmpty(prefix)) {
            fieldName = prefix.toLowerCase() + "." + fieldName;
        }
        return fieldName;
    }

    /**
     * 获取流水号和脚本计算结果
     *
     * @param list
     * @param data
     * @param actDefId
     * @return Map<String                                                                                                                                                                                                                                                               ,                                                                                                                                                                                                                                                               Object>
     * @since 1.0.0
     */
    private Map<String, Object> getCalculateDataMap(List<FormField> list, FormData data, String actDefId) {
        Map<String, Object> resultMap = new HashMap<String, Object>();
        SysUser user = (SysUser) ContextUtil.getCurrentUser();
        SysOrg org = (SysOrg) ContextUtil.getCurrentOrg();
        Position pos = (Position) ContextUtil.getCurrentPos();
        for (FormField field : list) {
            String fieldName = field.getFieldName().toLowerCase();
            // 值来源为流水号。
            if (field.getValueFrom() == FormField.VALUE_FROM_IDENTITY) {
                Identity identity = identityService.getByIdentityName(field.getIdentity());
                // 如果actDefId=#dataTem 则说明是业务数据模板调用数据,需要立刻获取流水号
                if ("#dataTem".equals(actDefId)) {
                    String id = identityService.nextId(identity.getAlias());
                    resultMap.put(fieldName, id);
                } else if ("#formPrev".equals(actDefId)) {
                    // 表单预览情况则不获取流水号
                } else {
                    // 处理启动流程时是否显示流水号的数据，判断依据来源于设计字段时，是否勾选了 启动时显示 isShowidentity=1 显示在启动页面, 为0则显示
                    String prop = field.getCtlProperty();
                    if (StringUtil.isNotEmpty(prop)) {
                        JSONObject jsonObject = JSONObject.fromObject(prop);
                        if (jsonObject.containsKey("isShowidentity")) {
                            String isShowidentity = jsonObject.getString("isShowidentity");
                            if ("1".equals(isShowidentity)) {
                                String id = identityService.nextId(identity.getAlias());
                                resultMap.put(fieldName, id);
                            }
                        }
                    }

                }
            }
            // 值来源为脚本。
            else if (field.getValueFrom() == FormField.VALUE_FROM_SCRIPT_SHOW) {
                Object result = FormUtil.calcuteField(field.getScript(), data.getMainFields());
                resultMap.put(fieldName, result);
            }
            // 计算默认日期数据
            else if (field.getControlType() == 15 || "date".equals(field.getFieldType())) {
                String prop = field.getCtlProperty();
                // {"format":"yyyy-MM-dd","displayDate":1,"condition":"like"}
                if (StringUtil.isNotEmpty(prop)) {
                    try {
                        JSONObject jsonObject = JSONObject.fromObject(prop);
                        if (jsonObject.containsKey("displayDate")) {
                            String format = jsonObject.getString("format");
                            String displayDate = jsonObject.getString("displayDate");
                            if ("1".equals(displayDate)) {
                                resultMap.put(fieldName, TimeUtil.getDateString(format));
                            }
                        }
                    } catch (Exception ex) {
                        logger.debug(ex.getMessage());
                    }
                }
            }
            // 用户选择器默认当前用户
            else if (field.getControlType().shortValue() == FieldPool.SELECTOR_USER_SINGLE) {
                String prop = field.getCtlProperty();
                if (StringUtil.isNotEmpty(prop)) {
                    JSONObject jsonObject = JSONObject.fromObject(prop);
                    if (jsonObject.containsKey("showCurUser")) {
                        String showCurUser = JSONObject.fromObject(prop).getString("showCurUser");
                        if ("1".equals(showCurUser)) {
                            if (field.getIsHidden() == 1) {
                                resultMap.put(fieldName, user.getUserId());
                            } else {
                                resultMap.put(fieldName, user.getFullName());
                            }
                        }
                    }

                }
            } else if (FieldPool.SELECTOR_ORG_SINGLE == field.getControlType().shortValue() && BeanUtils.isNotEmpty(org)) {
                String prop = field.getCtlProperty();
                if (StringUtil.isNotEmpty(prop)) {
                    JSONObject jsonObject = JSONObject.fromObject(prop);
                    if (jsonObject.containsKey("showCurOrg")) {
                        String showCurUser = JSONObject.fromObject(prop).getString("showCurOrg");
                        if ("1".equals(showCurUser)) {
                            if (field.getIsHidden() == 1) {
                                resultMap.put(fieldName, org.getOrgId());
                            } else {
                                resultMap.put(fieldName, org.getOrgName());
                            }
                        }
                    }

                }
            } else if (FieldPool.SELECTOR_POSITION_SINGLE == field.getControlType() && BeanUtils.isNotEmpty(pos)) {
                String prop = field.getCtlProperty();
                if (StringUtil.isNotEmpty(prop)) {
                    JSONObject jsonObject = JSONObject.fromObject(prop);
                    if (jsonObject.containsKey("showCurPos")) {
                        String showCurUser = JSONObject.fromObject(prop).getString("showCurPos");
                        if ("1".equals(showCurUser)) {
                            if (field.getIsHidden() == 1) {
                                resultMap.put(fieldName, pos.getPosId());
                            } else {
                                resultMap.put(fieldName, pos.getPosName());
                            }
                        }
                    }

                }
            }
            //值来源为表单输入
            else if (field.getValueFrom() == FormField.VALUE_FROM_FORM) {
                resultMap.put(fieldName, data.getMainFields().get(fieldName));
            }

        }
        return resultMap;
    }

    /**
     * 根据流程实例取得流程的意见。
     *
     * @param instanceId
     * @return
     * @throws TemplateException
     * @throws IOException
     */
    public Map<String, String> getFormOptionsByInstance(String instanceId) throws IOException, TemplateException {
        Map<String, String> map = new HashMap<String, String>();

        List<TaskOpinion> list = taskOpinionService.getByProcInstId(instanceId);
        for (TaskOpinion option : list) {
            if (StringUtil.isEmpty(option.getFieldName())) {
                continue;
            }
            String fieldName = option.getFieldName().toLowerCase();
            generateOpinion(option, fieldName, map);
        }
        return map;
    }

    private void generateOpinion(TaskOpinion opinion, String fieldName, Map<String, String> map) throws IOException, TemplateException {
        String resultOpinion = "";
        if (map.containsKey(fieldName)) {
            resultOpinion = map.get(fieldName);
        }
        resultOpinion += TaskOpinionService.getOpinion(opinion, true);
        map.put(fieldName, resultOpinion);
    }

    /**
     * 判断指定的表数据是否存在。
     *
     * @param dsName
     * @param tableName
     * @param pkValue
     * @return
     * @throws Exception
     */
    public boolean isExistsData(String dsName, String tableName, String pkName, String pkValue) throws Exception {

        Map<String, Object> data = getByKey(dsName, tableName, pkName, pkValue);
        if (BeanUtils.isEmpty(data)) {
            return false;
        } else {
            return true;
        }
    }

    /**
     * 根据表名和主键获取一行数据。
     *
     * @param tableName
     * @param pk
     * @return
     * @throws Exception
     */
    private Map<String, Object> getByKey(String dsName, String tableName, String pkName, String pk) throws Exception {
        JdbcTemplate jt = ServiceUtil.getJdbcTemplate(dsName);
        String sql = "select a.* from " + tableName + " a where " + pkName + "='" + pk + "'";

        Map<String, Object> map = null;
        try {
            map = jt.queryForMap(sql);
        } catch (Exception ex) {
            map = new HashMap<String, Object>();
        }
        return map;
    }


    /**
     * 获取表单密级
     * @param dsName 数据源名称
     * @param tableName 表名称
     * @param pkName 主键名称
     * @param pkValue 主键id
     * @return
     * @throws Exception
     */
    public int getPrivacyLevelByTable(String dsName, String tableName, String pkName, String pkValue) throws Exception {
        JdbcTemplate jt = ServiceUtil.getJdbcTemplate(dsName);
        String sql = "select dataPrivacyLevel from w_" + tableName + " where " + pkName + "='" + pkValue + "'";
        Integer level = jt.queryForObject(sql, Integer.class);
        if (BeanUtils.isNotEmpty(level)) {
            return level;
        }
        return 1;
    }

    /**
     * 将list转换为map对象。
     *
     * @param fieldList
     * @return
     */
    private Map<String, FormField> convertToMap(List<FormField> fieldList) {
        Map<String, FormField> map = new HashMap<>();
        for (FormField field : fieldList) {
            String fieldName = field.getFieldName().toLowerCase();
            map.put(fieldName, field);
        }
        return map;
    }

    /**
     * 删除业务数据。
     *
     * @param id
     * @param tableName
     */
    public void delByIdTableName(String id, String tableName) {
        String sql = "delete from " + tableName + " where id=" + id;
        jdbcTemplate.update(sql);
    }

    public void delByDsAliasAndTableName(String dsAlias, String fullTableName, String pk) throws Exception {
        String tableName = fullTableName;
        if (fullTableName.startsWith(TableModel.CUSTOMER_TABLE_PREFIX)) {
            tableName = tableName.replaceFirst(TableModel.CUSTOMER_TABLE_PREFIX, "");
        } else {
            fullTableName = TableModel.CUSTOMER_TABLE_PREFIX + tableName;
        }
        FormTable bpmFormTable = formTableService.getByAliasTableName(dsAlias, tableName);
        if (bpmFormTable != null) {
            String sql = "delete from " + fullTableName + " where " + bpmFormTable.getPkField() + "='" + pk + "'";
            JdbcTemplateUtil.execute(dsAlias, sql);
        }

    }

    /**
     * 处理动态表单数据
     *
     * @param formData
     * @throws Exception
     */
    @Transactional(rollbackFor = Exception.class)
    public void handFormData(FormData formData, String formKey, String jsonData) throws Exception {
        SysBusEvent busEvent = sysBusEventService.getByFormKey(formKey);
        handEvent(busEvent, true, formData, jsonData);
        processRunService.handFormData(formData, null, "");
        handEvent(busEvent, false, formData, jsonData);
    }


    /**
     * 处理后台脚本。
     *
     * @param busEvent
     * @param isBefore
     * @param bpmFormData
     */
    private void handEvent(SysBusEvent busEvent, boolean isBefore, FormData bpmFormData, String jsonData) {
        if (busEvent == null) {
            return;
        }
        String script;
        if (isBefore) {
            script = busEvent.getPreScript();
        } else {
            script = busEvent.getAfterScript();
        }
        if (StringUtil.isEmpty(script)) {
            return;
        }
        Map<String, Object> params = new HashMap<>(2);
        params.put("data", bpmFormData);
        params.put("jsonData", jsonData);
        groovyScriptEngine.execute(script, params);
    }

    /**
     * 根据主键查询列表数据。
     *
     * @param tableId
     * @param pkValue
     * @return
     * @throws Exception
     */
    public FormData getByKey(String tableId, String pkValue, boolean isHandleData) throws Exception {
        return getByKey(tableId, pkValue, null, null, isHandleData);
    }

    /**
     * 删除业务数据
     *
     * @param pkValue
     * @param bpmFormTable
     * @throws Exception
     */
    public void delById(String pkValue, FormTable bpmFormTable) throws Exception {
        //判断是不是外部表
        if (bpmFormTable.isExtTable()) {
            //外部表
            String sql = "delete from " + bpmFormTable.getTableName() + " where " + bpmFormTable.getPkField() + "='" + pkValue + "'";
            JdbcTemplateUtil.execute(bpmFormTable.getDsAlias(), sql);
        } else {
            //非外部表
            String sql = "delete from " + TableModel.CUSTOMER_TABLE_PREFIX + bpmFormTable.getTableName() + " where id='" + pkValue + "'";
            JdbcTemplateUtil.execute(sql);
        }
    }

    /**
     * 根据业务数据模板数据查找表字段信息
     * 再根据表字段信息对数据进行处理
     *
     * @param dataTemplate
     * @return
     */
    public List<Map<String, Object>> queryByDataTemplate(DataTemplate dataTemplate) {
        FormTable formTable = formTableService.getByTableId(dataTemplate.getTableId(), FormField.FIELD_NORMAL_HIDDEN);
        List<FormField> fieldList = formTable.getFieldList();
        List<Map<String, Object>> dataList = dataTemplate.getList();
        for (FormField formField : fieldList) {
            if (formField.getFieldType().equals(FormConstants.FIELD_TYPE_NUMBER)) {
                Map<String, String> paraMap = formField.getPropertyMap();
                Object isShowComdify = paraMap.get("isShowComdify");
                Object decimalValue = paraMap.get("decimalValue");
                Object coinValue = paraMap.get("coinValue");
                for (Map<String, Object> dataMap : dataList) {
                    Object obj = dataMap.get(formField.getFieldName());
                    String str = StringUtil.getNumber(obj, isShowComdify, decimalValue, coinValue);
                    dataMap.replace(formField.getFieldName(), str);
                }
            }
        }
        return dataList;
    }

    /**
     * 获取自定义表流程实例的业务数据 JSON ,Number类型不格式化
     * @param processRun 流程运行实例
     * @param businessKey 业务主键
     * @param nodeId 节点id
     */
    public String getBpmFormDataJson(ProcessRun processRun, String businessKey, String nodeId) {
        if (StringUtil.isEmpty(businessKey)) {
            throw new RuntimeException("获取业务json PK 不能为空");
        }
        if (processRun == null) {
            processRun = processRunService.getByBusinessKey(businessKey);
        }

        FormDef formDef = formDefService.getOneById(processRun.getFormDefId());
        Set<Class> excludeFormat = new HashSet<>();
        excludeFormat.add(Number.class);
        FormData data =  getBusinessDataByKey(formDef.getTableId(), businessKey, processRun.getProcDefId(), nodeId, excludeFormat);

        Map<String, Map> model = new HashMap<>();
        model.put("main", data.getMainFields());
        model.put("opinion", data.getOptions());
        model.put("sub", data.getSubTableMap());

        return com.alibaba.fastjson.JSONObject.toJSONString(model);
    }


    /**
     * 获取表单数据
     * @param tableId 表id
     * @param businessKey 主键
     * @param procDefId 流程定义id
     * @param nodeId 节点id
     * @param excludeFormat 格式化
     * @return
     */
    public FormData getBusinessDataByKey(String tableId, String businessKey, String procDefId, String nodeId, Set<Class> excludeFormat) {
        try {
            excludeFormatObjects.set(excludeFormat);
            return getByKey(tableId, businessKey, procDefId, nodeId, true);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            excludeFormatObjects.remove();

        }
    }
}
